<html>
<head>
	<style type="text/css">
		body {
			font-family: arial;
			line-height: 1.5em;
			font-size: 90%;
		}
		h2 {
			margin-top: 2em;
		}
		h3 {
			margin-top: 1.5em;
		}
		h4 {
			margin-top: 0;
			margin-bottom: 0;
			display: inline;
			margin-right: 0.25em;
		}
		ol, ul{
			margin-top: 0;
			margin-bottom: 0;
		}
		p {
			margin-top: 1em;
		}
		table {
			border-collapse: collapse;
		}
		table {
			width: 1026px;
		}
		.tip {
			width: 986px;
		}
		td {
			border: solid 1px #c0c0c0;
			padding: 10px;
			font-size: 0.8em;
			vertical-align: top;
		}

		.code {
			color: blue;
			font-family: Courier;
			font-size: 0.9em;
		}
		.CrossLink {
			font-size: 0.5em;
			margin-left: 0.4em;
			line-height: 1em;
			display: inline;
			color: transparent;
			background: url('Documentation_Files/toc.png');
			height: 20px;
			width: 20px;
			overflow: hidden;			
		}
		.highlight{
			background-color: yellow;
		}
		.mandatory {
			color: red;
		}
		.note {
			font-style: italic;
		}
		.optional {
			color: green;
		}
		.SubSection {
			margin-left: 1em;
		}
		.SubSection p a, .Subsection > a {
			display: block;
			margin-top: 0.2em;
		}
		.tab {
			width: 2.5em;
			display: inline-block;
		}
		.tip {
			padding: 20px;
			background-color: #ffffa0;
			font-size: 0.9em;
			font-style: italic;
			border: solid 1px #c0c0c0;
			margin-bottom: 1em;
		}
		div.tip {
			margin-top: 2em;
		}
	</style>
</head>
<body>
	<h2><a name="TOC">Table of Contents</a></h2>
	<div class="SubSection">
		<p>
			<a href="#Introduction">Introduction</a>
		</p>
		<p>
			<a href="#GetStarted">Get Started</a>
			<a href="#Legend">Legend</a>
			<a href="#GeneralNotes">General Notes</a>
		</p>
		<p>
			<a href="#Syntax">Syntax</a>
			<ul style="list-style-type:none">
				<li><a href="#Syntax_Clear">!Clear</a>
				<li><a href="#Syntax_ConvertToFormulas">!ConvertToFormulas</a>
				<li><a href="#Syntax_Debug">!Debug</a>
				<li><a href="#Syntax_Dummy">!Dummy</a>
				<li><a href="#Syntax_Extract">!Extract</a>
				<li><a href="#Syntax_Filter">!Filter</a>
				<li><a href="#Syntax_Form">!Form</a>
				<li><a href="#Syntax_Message">!Message</a>
				<li><a href="#Syntax_OnTrueMove">!OnTrueMove</a>
				<li><a href="#Syntax_RefreshPivots">!RefreshPivots</a>
				<li><a href="#Syntax_Run">!Run</a>
				<li><a href="#Syntax_Set">!Set</a>
				<li><a href="#Syntax_StopIfBlank">!StopIfBlank</a>
				<li><a href="#Syntax_Version">!Version</a>
			</ul>
		</p>
		<p>
			<a href="#AdditionalModules">Additional Modules</a>
			<a href="#StandardModules">Standard Modules</a>
		</p>
		<p>
			<a href="#KnownIssues">Known Issues</a>
		</p>
		<p>
			<a href="#HowTo">How To</a>
		</p>
		<p>
			<a href="#Licensing">Licensing</a>
		</p>
	</div>
	
	<h2><a name="Introduction">Introduction</a><a class="CrossLink" title="Back to TOC" href="#TOC">Back to TOC</a></h2>
	<p>ExcelBricks provides an easy way to automate common Excel tasks. Instead of writing VBA or recording macros you use high level commands entered in a Configuration sheet.</p>
	<p>For instance, there is a single command to 
		<ul>
			<li>get data from SQL Server and other Excel workbooks
			<li>add a formula to each row that is not blank
			<li>apply filters
			<li>refresh pivots
			<li>convert formulas to values, etc.
		</ul>
	</p>
	<p>You can also use (and even write your own) external modules for more customized functionality. For example
		<ul>
		     <li>split content from a column across multiple rows
		     <li>merge rows by collating data from a column
		     <li>extract words that match a pattern from a cell
		     <li>extract data from text based files
		     <li>generate a presentation from a workbook
		</ul>
	</p>
	<p>ExcelBricks has undergone very many iterations. While this doesn't mean 0 bugs :-), it does ensure a pretty high standard for reliability, error-handling and performance to start off.</p>
	
	<h2><a name="GetStarted">Get Started</a><a class="CrossLink" title="Back to TOC" href="#TOC">Back to TOC</a></h2>
	<p>To start running the high-level program, call the ExcelBricks.sRunProgram subroutine. This is usually done by putting it in the onclick event of a button (like in the template).<p>
	<div class="code">
		<span class="tab">&nbsp;</span>Private Sub CommandButton1_Click()<br/>
		<span class="tab">&nbsp;</span><span class="tab">&nbsp;</span><span class="highlight">Call ExcelBricks.sRunProgram</span>(<span class="mandatory">"Configuration1"</span>, "")<br/>
		<span class="tab">&nbsp;</span>End Sub
	</div>
	<p>The subroutine can be called multiple times, with different parameter values and from different events (the onclick events of 2 separate buttons, for instance).</p>
	<p>The subroutine takes 2 parameters
		<ol>
			<li>The name of the configuration sheet - commands start from the 2nd row</li>
			<li>(can be blank) Database, user, password sets.</li>
		</ol>
	</p>
	<p>The 2nd parameter is used only if there is an SQL Server !Extract command. It's a ; separated list of database-user-password sets. Something like </p>
	<div class="code">
		<span class="tab">&nbsp;</span><span class="optional">"<span class="highlight">Server1.DB1:User1:Password1</span>;Server2.*:User2:Password2;*.DB3:User3:Password3"</span>
	</div>
	<p>When ExcelBricks sees an !Extract command, it checks if the server and database (Input 2 and Input 3) is in this list. If it is, it uses the listed user and password to connect to that database. Otherwise, the user is prompted at run time. The * wildcard is supported and comparison stops at the first match. If the user is set to . ExcelBricks uses Windows Authentication and credentials of the currently logged in user.</p>
	
	<h2><a name="Legend">Legend</a><a class="CrossLink" title="Back to TOC" href="#TOC">Back to TOC</a></h2>
	<p>
		<div class="code">
			<span class="tab">&nbsp;</span>A command<br/>
			<span class="tab">&nbsp;</span><span class="mandatory">A mandatory parameter for the command or sub-command</span><br/>
			<span class="tab">&nbsp;</span><span class="optional">An optional parameter</span><br/>
			<span class="tab">&nbsp;</span><span>What we are talking about will be in <span class="highlight">like this</span></span>
		</div>
	</p>
	
	<h2><a name="GeneralNotes">General Notes</a><a class="CrossLink" title="Back to TOC" href="#TOC">Back to TOC</a></h2>
	<p>All command are case insensitive.</p>
	<p>In case of any error the program exits with a message indicating the step tha failed. Prior to displaying this message the run log is sdisplayed. The error-handler closes all open database connections and cleans all objects in the core module. Memory cleanup and disposal of connections in the external module(s) must be done in that module's error-handler</p>
	<p>The button in the template is configured with the hotkey 1 i.e. Alt + 1 runs is equivalent to clicking the button.</p>
	
	<h2><a name="Syntax">Syntax</a><a class="CrossLink" title="Back to TOC" href="#TOC">Back to TOC</a></h2>
	
	<h3><a name="Syntax_Clear">!Clear</a></h3>
	<p>Clears all values in the specified range.</p>
	<p class="note">Note - Only visible cells in the range will be cleared.</p>
	<table>
		<tr>
			<td width=25% class="code">
				<span class="highlight">!Clear<br />
				<span class="mandatory">Sheet1!E1</span></span>
			</td>
			<td width=35%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<p>Putting just the sheet name clears out the whole sheet</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Clear<br />
				<span class="mandatory"><span class="highlight">Sheet1</span></span>
			</td>
			<td width=35%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
		
	<p>The command accepts all range formats that Excel accepts</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Clear<br />
				<span class="mandatory"><span class="highlight">Sheet1!E2,Sheet1!F2</span></span>
			</td>
			<td width=35%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
		<tr>
			<td width=25% class="code">
				!Clear<br />
				<span class="mandatory"><span class="highlight">Sheet1!E2:E4</span></span>
			</td>
			<td width=35%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>		
	</table>

	<p>You need to follow the same conventions - if the sheet name has a space, enclose it in single quotes</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Clear<br />
				<span class="mandatory"><span class="highlight">'Sample Sheet'</span>!E2:E4</span>
			</td>
			<td width=35%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>

	<p>Single quotes must not be used if you have only the sheet name.</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Clear<br />
				<span class="mandatory"><span class="highlight">Sample Sheet</span></span>
			</td>
			<td width=35%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>

	<p>Expressions like [r+2], [r-3] and [r] evaluate to a value with r as the current row number.</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Clear<br />
				<span class="mandatory">Sheet1!E<span class="highlight">[r+3]</span>:E4</span>
			</td>
			<td width=35%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<p class="tip">This above methods of entering a range hold for every inbuild command that takes a range as a parameter (or sub-parameter)</p>
	
	<h3><a name="Syntax_ConvertFormulaToValues">!ConvertFormulaToValues</a></h3>
	<p>Converts all formulas in the sheet to values. </p>
	<p class="note">Note - converting formulas to values clears all filters on the worksheet</p>
	<table>
		<tr>
			<td width=25% class="code">
				!ConvertFormulaToValues<br />
				<span class="mandatory"><span class="highlight">Sheet1</span></span>
			</td>
			<td width=35%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<p>Converts the specified cell (or range) to values.</p>
	<table>
		<tr>
			<td width=25% class="code">
				<span class="highlight">!ConvertFormulaToValues<br />
				<span class="mandatory">Sheet1!C4</span></span>
			</td>
			<td width=35%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<p class="tip">Use this to convert a formula column generated by an !Extract command to values. Filtering with formulas takes much much longer because a calculation runs everytime you set or clear the filter.</p>
	
	<h3><a name="Syntax_Debug">!Debug</a></h3>
	<p>Turns on debug mode for the run. For subsequent commands the row number and command are shown in the status bar</p>
	<table>
		<tr>
			<td width=25% class="code">
				<span class="highlight">!Debug</span>
			</td>
			<td width=35%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<p>The optional parameter specifies how many seconds to pause before each command (upto a maximum of 5 seconds)</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Debug<br />
				<span class="optional"><span class="highlight">2</span></span>
			</td>
			<td width=35%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<h3><a name="Syntax_Dummy">!Dummy</a></h3>
	<p>A dummy statement doesn't do anything. Use it to replace commands. If a row with a command is deleted all row numbers in range parametes of subsequent commands may need to be updated (unless they are in the [r] format)</p>
	<table>
		<tr>
			<td width=25% class="code">
				<span class="highlight">!Dummy</span>
			</td>
			<td width=35%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<p>You can also use it to comment out commands</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Dummy<br />
				<span class="optional"><span class="highlight">!Extract<br />
				Sheet1!E2:E4</span></span>
			</td>
			<td width=35%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<h3><a name="Syntax_Extract">!Extract</a></h3>
	<p>Output the results of the query to the sheet specified. All values in the output range are cleared before the query is run.</p>
	<table>
		<tr>
			<td width=25% class="code">
				<span class="highlight">!Extract<br />
				<span class="mandatory">Sheet1</span></span>
			</td>
			<td width=35% class="code"><span class="highlight"><span class="mandatory">select * from USERS</span></span></td>
			<td width=20% class="code"><span class="highlight"><span class="mandatory">SQL Server</span></span></td>
			<td width=20% class="code"><span class="highlight"><span class="mandatory">Database name</span></span></td>
		</tr>
	</table>
	
	<p>If 2 consecutive Extract queries point to the same worksheet, data from the 2nd query is appended to the end of the 1st one.</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Extract<br />
				<span class="highlight"><span class="mandatory">Sheet1</span></span>
			</td>
			<td width=35% class="code"><span class="mandatory">select * from USERS</span></td>
			<td width=20% class="code"><span class="mandatory">SQL Server</span></td>
			<td width=20% class="code"><span class="mandatory">Database name</span></td>
		</tr>
		<tr>
			<td width=25% class="code">
				!Extract<br />
				<span class="mandatory"><span class="highlight">Sheet1</span></span>
			</td>
			<td width=35% class="code"><span class="mandatory">select * from OTHER_USERS</span></td>
			<td width=20% class="code"><span class="mandatory">SQL Server</span></td>
			<td width=20% class="code"><span class="mandatory">Database name</span></td>
		</tr>		
	</table>
	
	<div class="tip">
		<p>The first row of the worksheet is overwritten with the column names returned by the query.</p>
		<p>All cells in the output sheet retain the formatting they had before the query was run.</p>
	</div>

	<div class="tip">
		<p>If the column after the last field output by the query has a comment it is copied down to all the records. These column headers are not cleared when an !Extract runs. Use this to copy a formula down to all output rows. You can use the R1C1 or A1 notation, and named ranges.</p>
		<img src="Documentation_Files/extract_1.png" />
	</div>
	
	<p>If you put in a specific cell instead of a worksheet, the query outputs to the area with that cell as the top left corner. Column headers are not output in this case. Existing data is also not cleared, however it may be overwritten</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Extract<br />
				<span class="highlight"><span class="mandatory">Sheet1!E4</span></span>
			</td>
			<td width=35% class="code"><span class="mandatory">select CURRENT_SETTING from SETTINGS</span></td>
			<td width=20% class="code"><span class="mandatory">SQL Server</span></td>
			<td width=20% class="code"><span class="mandatory">Database name</span></td>
		</tr>
	</table>
	
	<p>To extract data from an excel sheet (.xls, .xlsx, .xlsm and .xlsb) add the word Excel at the end of the command and enter the (relative or absolute) workbook path instead of the SQL Server name</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Extract<br />
				<span class="mandatory">Sheet1</span><br />
				<span class="highlight"><span class="optional">Excel</span></span>
			</td>
			<td width=35% class="code"><span class="mandatory">select * from [Sheet1$]</span></td>
			<td width=20% class="code"><span class="highlight"><span class="mandatory">..\Book1.xls</span></span></td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<div class="tip">
		<ol>
			<li>Each worksheet is treated as a table - use [&lt;Sheetname&gt;$] to refer to a specific worksheet.</li>
			<li>Extraction from an excel is subject to the same limitations as a normal Excel Import Data
				<ol style="list-style-type:lower-alpha">
					<li>The column data type is decided by a sampling of the first n (decided by local registry settings) rows. Data from subsequent rows that do not match the column data format they will not be displayed.</li>
					<li>For memo fields (content > 255 characters) content beyond 255 characters will be clipped off.</li>
				</ol>
			</li>
		</ol>
	</div>
	
	<p>For memo fields, a confirmation prompt warning about possible data loss is displayed. Include the word "ignore" at the end of the command to suppress this warning.</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Extract<br />
				<span class="mandatory">Sheet1</span><br />
				<span class="optional">Excel</span><br />
				<span class="optional"><span class="highlight">Ignore</span></span>
			</td>
			<td width=35% class="code"><span class="mandatory">select * from [Sheet1$]</span></td>
			<td width=20% class="code"><span class="mandatory">..\Book1.xls</span></td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<p>To extract data from csv file, enter the folder path and use the file name in place of the table / worksheet name.</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Extract<br />
				<span class="mandatory">Sheet1</span><br />
				<span class="optional">Excel</span>
			</td>
			<td width=35% class="code"><span class="mandatory">select * from <span class="highlight">[file1.csv]</span></span></td>
			<td width=20% class="code"><span class="mandatory"><span class="highlight">..\</span></span></td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<p>To extract data from an Microsoft Access database add the word Access at the end of the command and enter the (relative or absolute) workbook path instead of the SQL Server name</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Extract<br />
				<span class="mandatory">Sheet1</span><br />
				<span class="optional"><span class="highlight">Access</span></span>
			</td>
			<td width=35% class="code"><span class="mandatory">select * from Table1</span></td>
			<td width=20% class="code"><span class="mandatory"><span class="highlight">..\Database1.accdb</span></span></td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<p class="tip">It is the value of the cell that is used in all the commands. You can have a formula that evaluates to a query (or a command) and it will work just as a string that represents a query (or command) would.</p>
	
	<h3><a name="Syntax_Filter">!Filter</a></h3>
	<p>Sets the filter for the specified sheet. Each filter condition is on  a separate line with the column name and condition separated by a semicolon.</p>
	<table>
		<tr>
			<td width=25% class="code">
				<span class="highlight">!Filter<br />
				<span class="mandatory">Sheet1</span></span>
			</td>
			<td width=35% class="code">
				<span class="highlight"><span class="mandatory">Defect Number;&lt;10</span><br/>
				<span class="optional">Priority;=High<br />
				Assigned To;doej</span>
			</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<p>Using the !Filter command without any conditions clears the filter for the specified sheet.</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Filter<br />
				<span class="mandatory">Sheet1</span>
			</td>
			<td width=35%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<h3><a name="Syntax_Form">!Form</a></h3>
	<p>This brings up a form with a line of text saying "Hello World"</p>
	<table>
		<tr>
			<td width=25% class="code">
				<span class="highlight">!Form<br />
				<span class="mandatory">Form Title</span></span>
			</td>
			<td width=35% class="code">
				<span class="highlight">Label;<span class="optional">Hello World!</span></span>
			</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<p>The 1st sub-parameter of the 2nd line can have 2 parts. The first is the title of the form and the 2nd (optional) part is the label of the submit button (the default value is Submit)</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Form<br />
				<span class="mandatory">A Different Button</span><span class="optional"><span class="highlight">:Next</span></span>
			</td>
			<td width=35% class="code">
				Label;<span class="optional">Hello World!</span>
			</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<p>An optional 2nd sub-parameter of the 2nd line allows you to set the width of the label and control section (minimum values are 80 and 165)</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Form<br />
				<span class="mandatory">Form Title</span><span class="optional"><span class="highlight">;100:300</span></span>
			</td>
			<td width=35% class="code">
				Label;<span class="optional">Hello World!</span>
			</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<p>The optional 3rd sub-parameter in the 2nd line is used to "fit" the form height. The height is automatically fit if there are more than 7 rows (including radio options)</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Form<br />
				<span class="mandatory">Form Title</span><span class="optional">;<span class="highlight">;fit</span></span>
			</td>
			<td width=35% class="code">
				Label;<span class="optional">Hello World!</span>
			</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<p>A blank line displays a blank line when showing the form</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Form<br />
				<span class="mandatory">Form with a blank line</span>
			</td>
			<td width=35% class="code">
				Label;<span class="optional">Hello</span><br />
				<br />
				Label;<span class="optional">World!</span>
			</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
		
	<p>You can also show a textbox. The 2nd sub-parameter is the label for that box. </p>
	<p>If there is a control in the form, a mandatory 3rd line indicates where the form's data goes to. (the values are stored vertically, with a line for each control, including labels)</p>
	<p>The default values for each control come from the same location.</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Form<br />
				<span class="mandatory">A Form with a textbox<br />
				<span class="highlight">Configuration1!E[r]</span></span>
			</td>
			<td width=35% class="code">
				<span class="highlight">Textbox;<span class="optional">Enter User ID</span></span>
			</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
		
	<p>You can also have a password field.</p>
	<p class="note">Note - it is advisable to clear the cell to which the password was sent immediately after it's used.</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Form<br />
				<span class="mandatory">A Form with a password box<br />
				Configuration1!E[r]</span>
			</td>
			<td width=35% class="code">
				<span class="highlight">Password;</span><span class="optional">Enter Password</span>
			</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
		
	<p>If the control label ends in * the field is mandatory. A form cannot be submitted unless all mandatory fields are filled.</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Form<br />
				<span class="mandatory">A Form with mandatory fields<br />
				Configuration1!E2</span>
			</td>
			<td width=35% class="code">
				Textbox;<span class="highlight"><span class="optional">Enter User ID*</span></span><br />
				Password;<span class="highlight"><span class="optional">Enter Password*</span></span><br />
				Textbox;<span class="optional">Enter coupon</span>
			</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
		
	<p>You can also add a checkbox. A checked box has value TRUE, an unchecked one is FALSE.</p>
	<p class="note">Note - Checkboxes cannot be mandatory.</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Form<br />
				<span class="mandatory">A Form with a checkbox<br />
				Configuration1!E2</span>
			</td>
			<td width=35% class="code">
				<span class="highlight">Checkbox;</span><span class="optional">Check this box</span>
			</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
		
	<p>Or a pulldown. The label has 2 parts separated by a :. The 2nd part is a pointer to the first cell in a list of options. All entries till the first blank cell (vertically) are taken.</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Form<br />
				<span class="mandatory">A Form with a pulldown<br />
				Configuration1!E2</span>
			</td>
			<td width=35% class="code">
				<span class="highlight">Pulldown;</span><span class="optional">List of values</span><span class="mandatory"><span class="highlight">:Configuration1!H2</span></span>
			</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
		
	<p>And radio buttons. Radio buttons are are configured just like pulldowns. If the default value is missing the first option is selected automatically.</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Form<br />
				<span class="mandatory">A Form with radio buttons<br />
				Configuration1!E2</span>
			</td>
			<td width=35% class="code">
				<span class="highlight">Radio;</span><span class="optional">Pick</span><span class="mandatory"><span class="highlight">:Configuration1!H2</span></span>
			</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
		
	<p>And radio buttons. Radio buttons are are configured just like pulldowns. If the default value is missing the first option is selected automatically.</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Form<br />
				<span class="mandatory">A Form with radio buttons<br />
				Configuration1!E2</span>
			</td>
			<td width=35% class="code">
				<span class="highlight">Radio;</span><span class="optional">Pick</span><span class="mandatory"><span class="highlight">:Configuration1!H2</span></span>
			</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
		
	<p>You can have any number of controls on a form. If you have less than 7 rows (including radio options) they align to the bottom of the form.</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Form<br />
				<span class="mandatory">A Form with lots of controls<br />
				Configuration1!E2</span>
			</td>
			<td width=35% class="code">
				Label;<span class="optional">Hello</span><br />
				Label;<br />
				Label;<span class="optional">World</span><br />
				Textbox;<span class="optional">Enter User ID</span><br />
				Password;<span class="optional">Enter Password</span><br />
				Radio;<span class="optional">Pick</span><span class="mandatory">:Configuration1!H2</span>
			</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<h3><a name="Syntax_Message">!Message</a></h3>
	<p>Displays a Yes / No message box. The 2nd line is the message. The 3rd line is where the output of the prompt goes to (TRUE if Yes, FALSE otherwise).</p>
	<table>
		<tr>
			<td width=25% class="code">
				<span class="highlight">!Message<br />
				<span class="mandatory">Had a nice day so far?<br />
				Configuration1!E[r]</span></span>
			</td>
			<td width=35%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<h3><a name="Syntax_OnTrueMove">!OnTrueMove</a></h3>
	<p>If the value in the 1st parameter range evaluates to True program control is moved 2nd parameter number of rows down </p>
	<table>
		<tr>
			<td width=25% class="code">
				<span class="highlight">!OnTrueMove<br />
				<span class="mandatory">Configuration1!E[r]<br />
				1</span></span>
			</td>
			<td width=35%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<p>If the 2nd parameter is negative control moves back that many steps.</p>
	<p class="note">Note - you can use negative values to do a loop</p>
	<table>
		<tr>
			<td width=25% class="code">
				!OnTrueMove<br />
				<span class="mandatory">Configuration1!E[r]<br />
				<span class="highlight">-1</span></span>
			</td>
			<td width=35%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	</a></h3><a name="Syntax_RefreshPivots">!RefreshPivots</a></h3>
	<p>Runs a Refresh Data on every pivot table in the specified sheet</p>
	<table>
		<tr>
			<td width=25% class="code">
				<span class="highlight">!RefreshPivots<br />
				<span class="mandatory">Sheet1</span></span>
			</td>
			<td width=35%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<h3><a name="Syntax_Run">!Run</a></h3>
	<p>You can add your own module to the workbook and run it's subroutines / functions.</p>
	<p>For subroutines use the !Run command followed by &lt;Module name&gt;.&lt;Subroutine name&gt;. The 2nd cell contains the arguments to the function - each argument on a new line.</p>
	<table>
		<tr>
			<td width=25% class="code">
				<span class="highlight">!Run<br />
				<span class="mandatory">Template.sTemplate</span></span>
			</td>
			<td width=35% class="code">
				<span class="mandatory"><span class="highlight">Hello<br/>
				2</span></span>
			</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<p>Functions can be called similarly. The 3rd line in the command cell is the cell to which the value returned by the function will go to.</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Run<br />
				<span class="mandatory">Template.fTemplate<br />
				<span class="highlight">Configuration1!E20</span></span>
			</td>
			<td width=35% class="code">
				<span class="mandatory">Hello<br />
				2</span>
			</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<h3><a name="Syntax_Set">!Set</a></h3>
	<p>Creates a variable called [myVariableName] and assigns the value &lt;my variable value&gt; to it. ExcelBricks will take the string [myVariableName] to mean whatever value it holds at that point of time.</p>
	<table>
		<tr>
			<td width=25% class="code">
				<span class="highlight">!Set<br />
				<span class="mandatory">[myVariableName]</span></span>
			</td>
			<td width=35% class="code">
				<span class="optional"><span class="highlight">&lt;my variable value&gt;</span></span>
			</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	<p class="tip">ExcelBricks variables cannot be used in Excel formulas - they are NOT equivalent to named ranges.</p>
	
	<p>Sets the Autofit configuration variable. True indicates that the worksheet columns should be autofit after an !Extract. Default is False</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Set<br />
				<span class="highlight"><span class="mandatory">Autofit</span></span>
			</td>
			<td width=35% class="code">
				<span class="mandatory"><span class="highlight">TRUE</span></span>
			</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<p>If the 2nd line is not a configuration variable the cell or range specified is set to the indicated value. To set the cell(s) to a formula use a string starting with =</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Set<br />
				<span class="highlight"><span class="mandatory">Configuration1!E[r]:G[r]</span></span>
			</td>
			<td width=35% class="code">
				<span class="optional"><span class="highlight">Hello World!</span></span>
			</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<p>Redirect ExcelBricks messages to the specified log file instead of &lt;Workbook name&gt;.log (when running in invisible mode)</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Set<br />
				<span class="highlight"><span class="mandatory">Log File</span></span>
			</td>
			<td width=35% class="code">
				<span class="mandatory"><span class="highlight">Log_16051105</span></span>
			</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<p>When "No Screen Refresh" is True the screen is updated only at the end of the run. </p>
	<p>By default, the screen is updated after the !Run command, and during the !Form command, password prompts and warnings. </p>
	<p>When there are a lot of objects on the screen (charts, frames, etc.) setting "No Screen Refresh" to True to eliminates flicker.</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Set<br />
				<span class="highlight"><span class="mandatory">No Screen Refresh</span></span>
			</td>
			<td width=35% class="code">
				<span class="mandatory"><span class="highlight">TRUE</span></span>
			</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<p>The "Run Timestamp Address" is the address of an (unlocked) cell to which the date and time of the run are entered on completion. If left blank the timestamp is not entered anywhere.</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Set<br />
				<span class="highlight"><span class="mandatory">Run Timestamp Address</span></span>
			</td>
			<td width=35% class="code">
				<span class="mandatory"><span class="highlight">'Control Panel'!B5</span></span>
			</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<p>The "Run Message" value is displayed on successful completion of a run. If left blank the default Run log is displayed.</p>
	<table>
		<tr>
			<td width=25% class="code">
				!Set<br />
				<span class="highlight"><span class="mandatory">Run Message</span></span>
			</td>
			<td width=35% class="code">
				<span class="mandatory"><span class="highlight">Run completed!</span></span>
			</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<h3><a name="Syntax_StopIfBlank">!StopIfBlank</a></h3>
	<p>The "Run Message" value is displayed on successful completion of a run. If left blank, the default Run log is displayed.</p>
	<table>
		<tr>
			<td width=25% class="code">
				<span class="highlight">!StopIfBlank<br />
				<span class="mandatory">Configuration1!C[r]:D[r]</span><br />
				<span class="optional">Invalid value entered</span></span>
			</td>
			<td width=35%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	
	<h3><a name="Syntax_Version">!Version</a></h3>
	<p>Displays the program version number, the ExcelBricks API version and the ExcelBricks Syntax version</p>
	<table>
		<tr>
			<td width=25% class="code">
				<span class="highlight">!Version</span>
			</td>
			<td width=35%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	
	<p class="tip">Use the ExcelBricks API and the ExcelBricks syntax version to check for backward compatibility. If the ExcelBricks API version is a.x+, any calls to the ExcelBricks.sRunProgram subroutine from version a.x and above will work with this version. Similarly, if the ExcelBricks Syntax version is b.x+, all syntax commands supported by version b.x and above are supported by this version.</p>
	
	
	<h2><a name="AdditionalModules">Additional Modules</a><a class="CrossLink" title="Back to TOC" href="#TOC">Back to TOC</a></h2>
	<p>Additional modules are called using the !Run command. They can be imported into the workbook VBProject (Alt + F11 > Ctrl + M). Each subroutine or function in the additional module should have a corresponding wrapper subroutine and wrapper function. This wrapper must 
		<ol>
			<li>Takes a single argument which is a LineFeed (Chr(10)) separated list of arguments for the called subroutine or function. 
			<li>Split this parameter into multiple parameters
			<li>Do a type conversion, if the specific subroutine or function argument is not a string
			<li>Call the actual subroutine or function with these (type converted) parameters
			<li>For functions, return the value of the called function
			<li>Pass on any error messages from the called function or subroutine via the ExcelBricks.lngGlobalErrNumber and ExcelBricks.strGlobalErrDescription global variables.
		</ol>
	</p>
	<p>Check the Template module to see how to build a wrapper for a subroutine (sTemplateWrapper for the sTemplate subroutine) and function (fTemplateWrapper for the fTemplate function).</p>
	<p>Error numbers 7000 to 7100 are reserved for ExcelBricks. Additional modules can use a subset of these error numbers to control error handling functionality. A full list of such error numbers and their effect on error handling is given below
		<ul style="list-style-type: none">
			<li>ExcelBricks.lngFullError - shows the standard log followed by a critical dialog with the error description. The behaviour 
			<li>ExcelBricks.lngShowError - shows a critical dialog with the error description
			<li>ExcelBricks.lngShowErrorNoLine - shows a critical dialog with the error description but excludes the command line number
			<li>ExcelBricks.lngShowWarning - shows a warning dialog with the error description
			<li>ExcelBricks.lngShowMessage - shows an information dilaog with the error description
			<li>ExcelBricks.lngShowGlobal - shows an information dialog with the ExcelBricks.strGlobalErrDescription value (instead of the error description)
			<li>ExcelBricks.lngShowStatus - show the status alone
		</ul>
	<p>
	<p>For example, to exit an additional module function with a warning message you would use </p>
	<div class="code">
		<span class="tab">&nbsp;</span>Err.Raise <span class="highlight">ExcelBricks.lngShowWarning</span>, , <span class="optional">"Don't forget to get milk."</span>
	</div>
	<p>ExcelBricks turns off screen updates and automatic calculation while processing a command (caculation is run between commands. Screen updates are run between commands if No Screen Refresh is not set). So, external modules that need screen updates or automatic calculations must explicitly turn these on when they need it by the following bit of code</p>
	<div class="code">
		<span class="tab">&nbsp;</span>Application.ScreenUpdating = True<br/>
		<span class="tab">&nbsp;</span>Application.Calculation = xlCalculationAutomatic
	</div>
	<p>ExcelBricks exposes a number of commonly used functions that can be used by additional modules. A full list of such functions is given below</p>
		<ul style="list-style-type: none">
			<p><h4>Clock functions</h4> - these functions provide acces to the timer that ExcelBricks uses to keep track of the execution time
			<ul style="list-style-type: none">
				<li>ExcelBricks.fGetTime - returns the total number of seconds since the start of the run.
				<li>ExcelBricks.sPauseClock - pauses the timer (for example, to exclude time spent waiting for user input)
				<li>ExcelBricks.sContinueClock - resumes the timer
			</ul></p>
			<p><h4>Range functions</h4>  - convert a string to an excel range
			<ul style="list-style-type: none">
				<li>ExcelBricks.fGetRange(strAddress) - returns the range specified by the string strAddress. You can use expressions like [r+2], [r-3] and [r] (as for the inbuilt commands). If you don't specify cell(s) you get a range spanning the entire worksheet.
				<li>ExcelBricks.fGetRangeFromCell(strStartAddress) - returns a columnar range starting at strStartAddress and ending at the first blank or 0 cell.
			</ul></p>
			<p><h4>Logging and messaging functions</h4> 
			<ul style="list-style-type: none">
				<li>ExcelBricks.fAddLogEntry - add an entry to the log. The log is displayed only when the Run Message (refer to !Set) is blank.
				<li>ExcelBricks.fMessage - equivalent to VB MsgBox, but avoids screen repainting issues (because screen updating is normally turned off for performance resons during a run). When ExcelBricks is run in invisible mode (for example, by using an external script file that starts Excel with .Visible = false), messages are appended to a file &lt;Workbook name&gt;.log.
			</ul></p>
			<p><h4>Path functions</h4> 
			<ul style="list-style-type: none">
				<li>ExcelBricks.fGetPath - returns an absolute path from a relative path (starting with .\ or ..\)
			</ul></p>
		</ul>
	</p>
	<p>For example, if an additional module function needs to show an informational message you would use </p>
	<div class="code">
		<span class="tab">&nbsp;</span><span class="highlight">ExcelBricks.fMessage</span> <span class="optional">"Hello World"</span>, vbInformation, <span class="optional">"Hello World Title"</span>
	</div>


	<h2><a name="StandardModules">Standard Modules</a><a class="CrossLink" title="Back to TOC" href="#TOC">Back to TOC</a></h2>
	<p>There are a few standard modules already available for commonly used functionality. These files (separate from the main excel workbook) can be imported if required and then used via the !Run command.</p>
	<p>The functionality and parameters for each modules' subroutines / functions take are documented as comments in the module's .bas file. The numbers preceding each argument indicate the order in the function call. </p>
	<p>For example the Pick module has the comment
	<div class="code" style="color:black">
		<span class="tab">&nbsp;</span>' <span class="highlight">fPickFile - shows a Choose file dialog with title 1.strTitle and file name filter 2.strFilter</span>
	</div>
	<p>A sample call to this function is </p>
	<table>
		<tr>
			<td width=25% class="code">
				!Run<br />
				<span class="mandatory"><span class="highlight">Pick.fPickFileWrapper</span><br />
				Configuration1!F1</span>
			</td>
			<td width=35% class="code">
				<span class="mandatory"><span class="highlight">Choose a file to import<br />
				*.xls</span></span>
			</td>
			<td width=20%>&nbsp;</td>
			<td width=20%>&nbsp;</td>
		</tr>
	</table>
	<p>Note that strTitle is the first argument, as indicated by the prefix 1. in the comment. The fPickFile function is called via the fPickFileWrapper function.</p>
	
	
	<h2><a name="KnownIssues">Known Issues</a><a class="CrossLink" title="Back to TOC" href="#TOC">Back to TOC</a></h2>
	<p>For the list of issues and requested enhancements check <a href="https://excelbricks.codeplex.com/workitem/list/basic">https://excelbricks.codeplex.com/workitem/list/basic</a></p>
	
	
	<h2><a name="HowTo">How To</a><a class="CrossLink" title="Back to TOC" href="#TOC">Back to TOC</a></h2>
	<p>In Excel 2007, macros may not enabled by default. So, you need to grant run permission to the ExcelBricks macro You do this by following the below steps</p>
	<ol>
		<li>
			<p>When you open the ExcelBricks workbook you will see a Security Warning (usually just below the ribbon). Click the "Options" button.</p>
			<p><img src="Documentation_Files/howto_1.png" /></p>
		</li>
		<li>
			<p>In the Microsoft Office Security Options dialog that opens up, select the "Enable this content" option and click the "OK" button.</p>
			<p><img src="Documentation_Files/howto_2.png" /></p>
		</li>
	</ol>
	<p>This will allow the ExcelBricks macro to run. You will need to do this everytime you open the ExcelBricks workbook unless you open it from a "trusted" location (Excel Options > Trust Center > Microsoft Office Excel Trust Center > Trust Center Settings > Trusted Locations).</p>
	<p>To add a button to a worksheet and link it to some code (that runs when it is clicked), see <a href="http://code.google.com/p/excelbricks/wiki/HowToAddButtonLinkCode">http://code.google.com/p/excelbricks/wiki/HowToAddButtonLinkCode</a></p>
	
	
	<h2><a name="Licensing">Licensing</a><a class="CrossLink" title="Back to TOC" href="#TOC">Back to TOC</a></h2>
	<p>ExcelBricks is free software: you can redistribute it and/or modify it under the terms of the "The MIT License" (see <a href="http://www.opensource.org/licenses/mit-license.php">http://www.opensource.org/licenses/mit-license.php</a>)</p>
	<p>Copyright (C) 2011 by <a href="mailto:potato.peelings@gmail.com">potato.peelings@gmail.com</a></p>
	<p>Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:</p>
	<p>The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.</p>
	<p>THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.</p>
	<p>The "Software" refers to the default ExcelBricks workbook and all standard modules that can be downloaded from the ExcelBricks project site. The "ExcelBricks project site" refers to <a href="https://code.google.com/p/excelbricks/">https://code.google.com/p/excelbricks/</a>, it's sub-folders and all content contained therein.</p>
</body>
</html>